import re
import time
from flask import request
from flask_restful import abort
from flask_login import current_user, login_user
from funcy import project
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.exc import IntegrityError
from funcy import partial

from redash import models, limiter
from redash.permissions import (
    require_admin,
)
from redash.handlers.base import (
    BaseResource,
    require_fields,
    get_object_or_404,
    paginate,
    order_results as _order_results,
)

from redash.authentication.account import (
    invite_link_for_user,
    send_invite_email,
    send_password_reset_email,
    send_verify_email,
)
from redash.settings import parse_boolean
from redash import settings

class WorkflowResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        bill = req["bill"]
        billid = req["billid"]
        billtype = req["billtype"]
        next_type = req["next_type"]
        message = req["message"]
        userid = req["userid"]
        billdate = req["billdate"]
        orgunitid = req["orgunitid"] if "orgunitid" in req else 0
        result = models.Workflow.workflowcheck(bill,billid,billtype,next_type,message,userid,billdate,orgunitid,self.current_org.id)
        models.db.session.commit()
        return result[0]

class WorkfloweditResource(BaseResource):
    def get(self,bill,billtype,billid,userid):
        result = models.Workflow.workflowedit(bill,billtype,billid,userid,self.current_org.id)
        return result[0]

class WorkflowmessageResource(BaseResource):
    def get(self, bill, billid,bill_type,orgunitid):
        result = models.Workflow.workflowmessage(bill, billid,bill_type,orgunitid)
        return result[0]

class WorkflowNewResource(BaseResource):
    def get(self):
        result = models.Workflow.get_id()
        return result[0]

    @require_admin
    def post(self):
        req = request.get_json(force=True)
        require_fields(req, ("bill_enum","name"))

        workflow = models.Workflow(
        id=req["id"],
        org=self.current_org,
        userid=req["userid"],
        name = req["name"],
        orgunitid  = req["orgunitid"],
        bill_enum = req["bill_enum"],
        bill_type = req["bill_type"],
        is_active = req["is_active"],
        )
        try:
            models.db.session.add(workflow)
            models.db.session.commit()
        except IntegrityError as e:
            if "name" in str(e):
                abort(400, message="编号已存在.")
            abort(500)


class WorkflowUpdateResource(BaseResource):
    def get(self, workflow_id):
        result = models.Workflow.get_by_id(workflow_id)
        return result.to_dict()

    @require_admin
    def post(self, workflow_id):
        req = request.get_json(force=True)
        require_fields(req, ("bill_enum","name"))

        workflow = models.Workflow.get_by_id(workflow_id)
        params = dict(bill_enum = req["bill_enum"],
                name=req["name"],bill_type=req["bill_type"],
                orgunitid  = req["orgunitid"],is_active=req["is_active"])
        try:
            self.update_model(workflow, params)
            models.db.session.commit()
        except IntegrityError as e:
            if "name" in str(e):
                abort(400, message="名称已存在.")
            abort(500)
class WorkflowUpdatedeleteResource(BaseResource):
    @require_admin
    def post(self, workflow_id):
        workflow =  models.Workflow.get_by_id(workflow_id)
        workflow_bill_state = models.Workflow_bill_state.get_by_workflowid(workflow_id)
        workflow_transition = models.Workflow_transition.get_by_workflowid(workflow_id)
        models.db.session.delete(workflow_bill_state)
        models.db.session.delete(workflow_transition)
        models.db.session.delete(workflow)
        models.db.session.commit()

class WorkflowListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        page = req["page"]
        page_size = req["pagesize"]
        name = req["name"] if "name" in req else ""
        bill_enum = req["bill_enum"] if "bill_enum" in req else ""
        bill_type = str(req["bill_type"]) if "bill_type" in req else ""
        strwhere ="t1.org_id=" +str(self.current_org.id)
        if name !='' :
            strwhere = strwhere + " and t1.name like '%"+name+"%'"
        if bill_enum !='' :
            strwhere = strwhere + " and  t1.bill_enum ='"+bill_enum+"'"
        if bill_type !='' :
            strwhere = strwhere + " and  t1.bill_type ='"+bill_type+"'"
        strgetfield ="""  t1.*,case when t1.is_active then '已启用'  else '已停用' end strstatus,
            t4.name as billname,COALESCE(t5.name,'全部') as orgunitname """

        tblname = """ public.workflow t1  left join public.tabletype t4 on t1.bill_enum =t4.code and t1.bill_type=t4.type
            left join public.orgunit t5 on t1.orgunitid=t5.id """
        orderfield ="t1.id"
        result = models.Enums.get_pagequery(tblname,strgetfield,orderfield,page_size,page,0,strwhere)
        return result[0]

class WorkflowcheckListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        page = req["page"]
        page_size = req["pagesize"]
        seltype = req["seltype"] #1 待审核-,2 已审核
        bill_enum = req["bill_enum"] if "bill_enum" in req else ''
        bill_type = req["bill_type"]  if "bill_type" in req else ''
        userid = str(req["userid"])
        code = req["code"] if "code" in req else ''
        name = req["name"] if "name" in req else ''
        tablename = req["tablename"] if "tablename" in req else ''
        order = req["order"] if "order" in req else 1
        orderfield = req["field"] if "field" in req else ""
        if orderfield=="code" :
            orderfield ="AB.code"
        elif orderfield=="bill_enum":
            orderfield ="AB.bill_enum"
        elif orderfield=="name":
            orderfield ="AB.name"
        elif orderfield=="bill_dateformat":
            orderfield ="AB.bill_dateformat"
        else:
            orderfield ="AB.code"
        
        strwhere =" 1=1 "
        if bill_type != "":
            strwhere = strwhere + " and AA.bill_type= '"+bill_type+"'"
        if bill_enum !='' :
            strwhere = strwhere + " and AA.bill_enum = '"+bill_enum+"'"
        if code !='' :
            strwhere = strwhere + " and  AB.code like '%"+code+"%'"
        if name !='' :
            strwhere = strwhere + " and  AB.name like '%"+name+"%'"
        if tablename !='' :
            strwhere = strwhere + " and  t10.name like '%"+tablename+"%'"

        strgetfield =""" AA.id as rowid,  AB.*,case when AB.status=2 then '审批结束' else coalesce(t4.name,'待提交') end as statename,
            t10.name as tablename """

        tblname = """  (select t1.bill_enum,t1.bill_type,t1.billid,t1.workflow_stateid,t2.id
            From public.workflow_bill_state t1 inner join  public.workflow_action t2 on t1.id =t2.workflow_bill_stateid and t2.status=0
                where ('""" + userid + """' = ANY(t1.allow_user) or t1.allow_group && (select groups from users where id='""" + userid + """')) and t1.is_active=true) AA
                inner join tabletype t3 on AA.bill_enum=t3.code and AA.bill_type=t3.type  inner join
                (select 'employee' as bill_enum,t1.openseapool as bill_type,t1.id,t1.org_id,
                to_char(t1.bill_date,'YYYY-MM-DD') bill_dateformat,t1.code,t1.name,
                t1.major,t1.certificate,t1.location,t1.message,t1.bytstatus,t1.status,t1.openseapool,
                COALESCE(t2.name,'') as username,COALESCE(t3.name,'') as maintainername,COALESCE(t4.name,'')  as signatoryname,
                t1.orgunitid
                from public.employee t1 left join public.users t2 on t1.userid=t2.id
                left join public.users t3 on t1.maintainer=t3.id
                left join public.users t4 on t1.signatory=t4.id
                union all
                select 'project' as bill_enum,0 as bill_type,t1.id,t1.org_id,
                                to_char(t1.bill_date,'YYYY-MM-DD') bill_dateformat,t1.code,t1.name,
                                t1.major,t1.certificate,t1.location,t1.message,t1.bytstatus,t1.status,0 as openseapool,
                                COALESCE(t2.name,'') as username,COALESCE(t3.name,'') as maintainername,COALESCE(t4.name,'')  as signatoryname,
                                t1.orgunitid
                                from public.project t1 left join public.users t2 on t1.userid=t2.id
                                left join public.users t3 on t1.userid=t3.id
                                left join public.users t4 on t1.signatory=t4.id
                union all
                select 'projectdetail' as bill_enum,0 as bill_type,t5.id,t1.org_id,
                                to_char(t1.bill_date,'YYYY-MM-DD') bill_dateformat,t5.code,t5.name,
                                t5.major,t5.certificate,t5.location,t1.message,t1.bytstatus,t5.status,0 as openseapool,
                                COALESCE(t2.name,'') as username,COALESCE(t3.name,'') as maintainername,COALESCE(t4.name,'')  as signatoryname,
                                t1.orgunitid
                                from public.project t1 inner join public.projectdetail t5 on t1.id=t5.projectid
								left join public.users t2 on t1.userid=t2.id
                                left join public.users t3 on t1.userid=t3.id
                                left join public.users t4 on t1.signatory=t4.id
                union all	
                select 'payment' as bill_enum,type as bill_type,t1.id,t1.org_id,
                                to_char(t1.bill_date,'YYYY-MM-DD') bill_dateformat,t1.code,t1.name,
                                array[''] as major,t4.certificate, '' as location,t1.message,'' as bytstatus,t1.status,0 as openseapool,
                                COALESCE(t2.name,'') as username,COALESCE(t3.name,'') as maintainername,''  as signatoryname,
                                t1.orgunitid
                                from public.payment t1 left join public.users t2 on t1.userid=t2.id
                                left join public.users t3 on t1.userid=t3.id
                                left join(select array_agg(t1.certificate) as certificate,t2.paymentid
								From public.project t1 inner join public.paymentactivity t2 on t1.id=t2.projectid
								group by t2.paymentid) t4 on t1.id=t4.paymentid) AB
                on AA.bill_enum=AB.bill_enum and AA.bill_type=AB.bill_type and AA.billid=AB.id and AB.org_id=""" +str(self.current_org.id) +"""
                left join public.workflow_state t4 on AA.workflow_stateid=t4.id
                left join public.tabletype t10 on AB.bill_enum=t10.code and AB.bill_type=t10.type """
        if seltype == "2" :
            tblname = """ (select max(t1.id) as id,max(updated_at) as updated_at,billid,bill_enum,max(status) as status,bill_type,workflow_stateid
                     from public.workflow_action t1 where t1.status in(1,2) and
                     t1.completed_by='""" + userid + """'  group by billid,bill_enum,bill_type,workflow_stateid) AA
                    inner join tabletype t3 on AA.bill_enum=t3.code and AA.bill_type=t3.type  inner join
                (select 'employee' as bill_enum,t1.openseapool as bill_type,t1.id,t1.org_id,
                to_char(t1.bill_date,'YYYY-MM-DD') bill_dateformat,t1.code,t1.name,
                t1.major,t1.certificate,t1.location,t1.message,t1.bytstatus,t1.status,t1.openseapool,
                COALESCE(t2.name,'') as username,COALESCE(t3.name,'') as maintainername,COALESCE(t4.name,'')  as signatoryname
                from public.employee t1 left join public.users t2 on t1.userid=t2.id
                left join public.users t3 on t1.maintainer=t3.id
                left join public.users t4 on t1.signatory=t4.id
                union all
                select 'project' as bill_enum,0 as bill_type,t1.id,t1.org_id,
                                to_char(t1.bill_date,'YYYY-MM-DD') bill_dateformat,t1.code,t1.name,
                                t1.major,t1.certificate,t1.location,t1.message,t1.bytstatus,t1.status,0 as openseapool,
                                COALESCE(t2.name,'') as username,COALESCE(t3.name,'') as maintainername,COALESCE(t4.name,'')  as signatoryname
                                from public.project t1 left join public.users t2 on t1.userid=t2.id
                                left join public.users t3 on t1.userid=t3.id
                                left join public.users t4 on t1.signatory=t4.id
                union all
                select 'projectdetail' as bill_enum,0 as bill_type,t5.id,t1.org_id,
                                to_char(t1.bill_date,'YYYY-MM-DD') bill_dateformat,t5.code,t5.name,
                                t5.major,t5.certificate,t5.location,t1.message,t1.bytstatus,t5.status,0 as openseapool,
                                COALESCE(t2.name,'') as username,COALESCE(t3.name,'') as maintainername,COALESCE(t4.name,'')  as signatoryname
                                from public.project t1 inner join public.projectdetail t5 on t1.id=t5.projectid
								left join public.users t2 on t1.userid=t2.id
                                left join public.users t3 on t1.userid=t3.id
                                left join public.users t4 on t1.signatory=t4.id
                union all	
                select 'payment' as bill_enum,type as bill_type,t1.id,t1.org_id,
                                to_char(t1.bill_date,'YYYY-MM-DD') bill_dateformat,t1.code,t1.name,
                                array[''] as major,t4.certificate, '' as location,t1.message,'' as bytstatus,t1.status,0 as openseapool,
                                COALESCE(t2.name,'') as username,COALESCE(t3.name,'') as maintainername,''  as signatoryname
                                from public.payment t1 left join public.users t2 on t1.userid=t2.id
                                left join public.users t3 on t1.userid=t3.id
                                left join(select array_agg(t1.certificate) as certificate,t2.paymentid
								From public.project t1 inner join public.paymentactivity t2 on t1.id=t2.projectid
								group by t2.paymentid) t4 on t1.id=t4.paymentid) AB
                on AA.bill_enum=AB.bill_enum  and AA.billid=AB.id and AB.org_id=""" +str(self.current_org.id) +"""
                left join workflow_state t4 on AA.workflow_stateid=t4.id
                left join public.tabletype t10 on AB.bill_enum=t10.code and AB.bill_type=t10.type """
        result = models.Enums.get_pagequery(tblname,strgetfield,orderfield,page_size,page,order,strwhere)
        return result[0]

class WorkflowcheckmessagResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        billid = req["billid"]
        bill = req["bill"]
        typeid = req["typeid"]#0-审核批注信息,1审签表查询
        result = models.Workflow.getworkflowmessage(bill,billid,typeid,self.current_org.id)
        return result[0]
